"""QGIS Unit tests for the Oracle provider.

.. note:: This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; either version 2 of the License, or
(at your option) any later version.
"""
__author__ = 'Nyall Dawson'
__date__ = '2016-07-06'
__copyright__ = 'Copyright 2016, The QGIS Project'

import os
import re

from qgis.PyQt.QtCore import QDate, QDateTime, QTime, QVariant
from qgis.PyQt.QtSql import QSqlDatabase, QSqlQuery
from qgis.core import (
    NULL,
    Qgis,
    QgsCoordinateReferenceSystem,
    QgsCredentials,
    QgsDataProvider,
    QgsFeature,
    QgsFeatureRequest,
    QgsField,
    QgsFields,
    QgsGeometry,
    QgsProject,
    QgsProjUtils,
    QgsProviderRegistry,
    QgsSettings,
    QgsTransactionGroup,
    QgsVectorLayer,
    QgsVectorLayerExporter,
    QgsWkbTypes,
)
import unittest
from qgis.testing import start_app, QgisTestCase

from providertestbase import ProviderTestCase
from utilities import compareWkt, unitTestDataPath

start_app()
TEST_DATA_DIR = unitTestDataPath()


class TestPyQgsOracleProvider(QgisTestCase, ProviderTestCase):

    @classmethod
    def setUpClass(cls):
        """Run before all tests"""
        super(TestPyQgsOracleProvider, cls).setUpClass()
        cls.dbconn = "host=localhost dbname=XEPDB1 port=1521 user='QGIS' password='qgis'"
        if 'QGIS_ORACLETEST_DB' in os.environ:
            cls.dbconn = os.environ['QGIS_ORACLETEST_DB']
        # Create test layers
        cls.vl = QgsVectorLayer(
            cls.dbconn + ' sslmode=disable key=\'pk\' srid=4326 type=POINT table="QGIS"."SOME_DATA" (GEOM) sql=', 'test', 'oracle')
        assert cls.vl.isValid()
        cls.source = cls.vl.dataProvider()
        cls.poly_vl = QgsVectorLayer(
            cls.dbconn + ' sslmode=disable key=\'pk\' srid=4326 type=POLYGON table="QGIS"."SOME_POLY_DATA" (GEOM) sql=', 'test', 'oracle')
        assert cls.poly_vl.isValid()
        cls.poly_provider = cls.poly_vl.dataProvider()

        cls.conn = QSqlDatabase.addDatabase('QOCISPATIAL', "oracletest")
        cls.conn.setDatabaseName('localhost/XEPDB1')
        if 'QGIS_ORACLETEST_DBNAME' in os.environ:
            cls.conn.setDatabaseName(os.environ['QGIS_ORACLETEST_DBNAME'])
        cls.conn.setUserName('QGIS')
        cls.conn.setPassword('qgis')
        assert cls.conn.open()

    def execSQLCommand(self, sql, ignore_errors=False):
        self.assertTrue(self.conn)
        query = QSqlQuery(self.conn)
        res = query.exec(sql)
        if not ignore_errors:
            self.assertTrue(res, sql + ': ' + query.lastError().text())
        query.finish()

    def getSource(self):
        # create temporary table for edit tests

        self.execSQLCommand('ALTER TABLE "QGIS"."EDIT_DATA" MODIFY "pk" DROP IDENTITY', ignore_errors=True)
        self.execSQLCommand('DROP TABLE "QGIS"."EDIT_DATA"', ignore_errors=True)
        self.execSQLCommand("""CREATE TABLE QGIS.EDIT_DATA ("pk" INTEGER GENERATED by default ON null as IDENTITY(START WITH 1 INCREMENT BY 1) PRIMARY KEY, "cnt" INTEGER, "name" VARCHAR2(100), "name2" VARCHAR2(100), "num_char" VARCHAR2(100), "dt" TIMESTAMP, "date" DATE, "time" VARCHAR2(100), GEOM SDO_GEOMETRY)""")
        self.execSQLCommand("""DELETE FROM user_sdo_geom_metadata  where TABLE_NAME = 'EDIT_DATA'""")
        self.execSQLCommand(
            """INSERT INTO user_sdo_geom_metadata (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID) VALUES ( 'EDIT_DATA', 'GEOM', sdo_dim_array(sdo_dim_element('X',-75,-55,0.005),sdo_dim_element('Y',65,85,0.005)),4326)""", ignore_errors=True)
        self.execSQLCommand("""CREATE INDEX edit_data_spatial_idx ON QGIS.EDIT_DATA(GEOM) INDEXTYPE IS MDSYS.SPATIAL_INDEX""")
        self.execSQLCommand("""INSERT INTO QGIS.EDIT_DATA ("pk", "cnt", "name", "name2", "num_char", "dt", "date", "time", GEOM)
      SELECT 5, -200, NULL, 'NuLl', '5', TIMESTAMP '2020-05-04 12:13:14', DATE '2020-05-02','12:13:01', SDO_GEOMETRY( 2001,4326,SDO_POINT_TYPE(-71.123, 78.23, NULL), NULL, NULL) from dual
  UNION ALL SELECT 3,  300, 'Pear', 'PEaR', '3', NULL, NULL, NULL, NULL from dual
  UNION ALL SELECT 1,  100, 'Orange', 'oranGe', '1', TIMESTAMP '2020-05-03 12:13:14', DATE '2020-05-03','12:13:14', SDO_GEOMETRY( 2001,4326,SDO_POINT_TYPE(-70.332, 66.33, NULL), NULL, NULL) from dual
  UNION ALL SELECT 2,  200, 'Apple', 'Apple', '2', TIMESTAMP '2020-05-04 12:14:14', DATE '2020-05-04','12:14:14', SDO_GEOMETRY( 2001,4326,SDO_POINT_TYPE(-68.2, 70.8, NULL), NULL, NULL) from dual
  UNION ALL SELECT 4,  400, 'Honey', 'Honey', '4', TIMESTAMP '2021-05-04 13:13:14', DATE '2021-05-04','13:13:14', SDO_GEOMETRY( 2001,4326,SDO_POINT_TYPE(-65.32, 78.3, NULL), NULL, NULL) from dual""")
        vl = QgsVectorLayer(
            self.dbconn + ' sslmode=disable key=\'pk\' srid=4326 type=POINT table="QGIS"."EDIT_DATA" (GEOM) sql=',
            'test', 'oracle')
        return vl

    def treat_time_as_string(self):
        return True

    def treat_date_as_datetime(self):
        return True

    def getEditableLayer(self):
        return self.getSource()

    def enableCompiler(self):
        QgsSettings().setValue('/qgis/compileExpressions', True)
        return True

    def disableCompiler(self):
        QgsSettings().setValue('/qgis/compileExpressions', False)

    def uncompiledFilters(self):
        filters = {
            '(name = \'Apple\') is not null',
            '"name" || \' \' || "name" = \'Orange Orange\'',
            '"name" || \' \' || "cnt" = \'Orange 100\'',
            '\'x\' || "name" IS NOT NULL',
            '\'x\' || "name" IS NULL',
            'false and NULL',
            'true and NULL',
            'NULL and false',
            'NULL and true',
            'NULL and NULL',
            'false or NULL',
            'true or NULL',
            'NULL or false',
            'NULL or true',
            'NULL or NULL',
            'not null',
            'radians(cnt) < 2',
            'degrees(pk) <= 200',
            'atan2(3.14, pk) < 1',
            'pk < pi()',
            'log10(pk) < 0.5',
            'pk < pi() / 2',
            'pk = char(51)',
            'pk = coalesce(NULL,3,4)',
            'name = trim(\'   Apple   \')',
            'x($geometry) < -70',
            'y($geometry) > 70',
            'xmin($geometry) < -70',
            'ymin($geometry) > 70',
            'xmax($geometry) < -70',
            'ymax($geometry) > 70',
            'disjoint($geometry,geom_from_wkt( \'Polygon ((-72.2 66.1, -65.2 66.1, -65.2 72.0, -72.2 72.0, -72.2 66.1))\'))',
            'intersects($geometry,geom_from_wkt( \'Polygon ((-72.2 66.1, -65.2 66.1, -65.2 72.0, -72.2 72.0, -72.2 66.1))\'))',
            'contains(geom_from_wkt( \'Polygon ((-72.2 66.1, -65.2 66.1, -65.2 72.0, -72.2 72.0, -72.2 66.1))\'),$geometry)',
            'distance($geometry,geom_from_wkt( \'Point (-70 70)\')) > 7',
            'intersects($geometry,geom_from_gml( \'<gml:Polygon srsName="EPSG:4326"><gml:outerBoundaryIs><gml:LinearRing><gml:coordinates>-72.2,66.1 -65.2,66.1 -65.2,72.0 -72.2,72.0 -72.2,66.1</gml:coordinates></gml:LinearRing></gml:outerBoundaryIs></gml:Polygon>\'))',
            'x($geometry) < -70',
            'y($geometry) > 79',
            'xmin($geometry) < -70',
            'ymin($geometry) < 76',
            'xmax($geometry) > -68',
            'ymax($geometry) > 80',
            'area($geometry) > 10',
            'perimeter($geometry) < 12',
            'relate($geometry,geom_from_wkt( \'Polygon ((-68.2 82.1, -66.95 82.1, -66.95 79.05, -68.2 79.05, -68.2 82.1))\')) = \'FF2FF1212\'',
            'relate($geometry,geom_from_wkt( \'Polygon ((-68.2 82.1, -66.95 82.1, -66.95 79.05, -68.2 79.05, -68.2 82.1))\'), \'****F****\')',
            'crosses($geometry,geom_from_wkt( \'Linestring (-68.2 82.1, -66.95 82.1, -66.95 79.05)\'))',
            'overlaps($geometry,geom_from_wkt( \'Polygon ((-68.2 82.1, -66.95 82.1, -66.95 79.05, -68.2 79.05, -68.2 82.1))\'))',
            'within($geometry,geom_from_wkt( \'Polygon ((-75.1 76.1, -75.1 81.6, -68.8 81.6, -68.8 76.1, -75.1 76.1))\'))',
            'overlaps(translate($geometry,-1,-1),geom_from_wkt( \'Polygon ((-75.1 76.1, -75.1 81.6, -68.8 81.6, -68.8 76.1, -75.1 76.1))\'))',
            'overlaps(buffer($geometry,1),geom_from_wkt( \'Polygon ((-75.1 76.1, -75.1 81.6, -68.8 81.6, -68.8 76.1, -75.1 76.1))\'))',
            'intersects(centroid($geometry),geom_from_wkt( \'Polygon ((-74.4 78.2, -74.4 79.1, -66.8 79.1, -66.8 78.2, -74.4 78.2))\'))',
            'intersects(point_on_surface($geometry),geom_from_wkt( \'Polygon ((-74.4 78.2, -74.4 79.1, -66.8 79.1, -66.8 78.2, -74.4 78.2))\'))',
            '"dt" < make_date(2020, 5, 4)',
            '"dt" = to_datetime(\'000www14ww13ww12www4ww5ww2020\',\'zzzwwwsswwmmwwhhwwwdwwMwwyyyy\')',
            '"date" >= make_date(2020, 5, 4)',
            '"date" = to_date(\'www4ww5ww2020\',\'wwwdwwMwwyyyy\')',
            'to_time("time") >= make_time(12, 14, 14)',
            'to_time("time") = to_time(\'000www14ww13ww12www\',\'zzzwwwsswwmmwwhhwww\')'
        }
        return filters

    def testAddFeatureWrongGeomType(self):
        """
        We override this test for Oracle provider, because Oracle DBs don't care
        about geometry type constraints
        """
        pass

    def testCrs(self):
        """
        We override this test for Oracle provider, because without PROJ >= 7
        Oracle is not able to understand correctly some EPSG code (4326 for instance)
        """
        # TODO remove this when PROJ will be >= 7
        if QgsProjUtils.projVersionMajor() >= 7:
            super().testCrs()

    # HERE GO THE PROVIDER SPECIFIC TESTS
    def testDateTimeTypes(self):
        vl = QgsVectorLayer('%s table="QGIS"."DATE_TIMES" sql=' %
                            (self.dbconn), "testdatetimes", "oracle")
        self.assertTrue(vl.isValid())

        fields = vl.dataProvider().fields()
        self.assertEqual(fields.at(fields.indexFromName(
            'date_field')).type(), QVariant.DateTime)
        self.assertEqual(fields.at(fields.indexFromName(
            'datetime_field')).type(), QVariant.DateTime)

        f = next(vl.getFeatures(QgsFeatureRequest()))

        date_idx = vl.fields().lookupField('date_field')
        self.assertIsInstance(f.attributes()[date_idx], QDateTime)
        self.assertEqual(f.attributes()[date_idx], QDateTime(2004, 3, 4, 0, 0, 0))
        datetime_idx = vl.fields().lookupField('datetime_field')
        self.assertIsInstance(f.attributes()[datetime_idx], QDateTime)
        self.assertEqual(f.attributes()[datetime_idx], QDateTime(
            QDate(2004, 3, 4), QTime(13, 41, 52)))

    def testDateInsertion(self):
        # fix for https://github.com/qgis/QGIS/issues/27087
        vl = QgsVectorLayer('%s table="QGIS"."DATE_TIMES" sql=' %
                            (self.dbconn), "testdatetimes", "oracle")
        self.assertTrue(vl.isValid())

        for f in vl.getFeatures():
            pass

        new_id = 2

        # delete the feature #2 if it is already present
        vl.dataProvider().deleteFeatures([new_id])

        # add a new feature
        newf = QgsFeature(f.fields(), new_id)
        date_idx = vl.fields().lookupField('date_field')
        dt = QDate(2019, 10, 15)
        newf.setAttribute(0, new_id)
        newf.setAttribute(date_idx, dt)
        success, featureAdded = vl.dataProvider().addFeatures([newf])
        self.assertTrue(success)

        # clean up
        vl.dataProvider().deleteFeatures([new_id])

    def testDefaultValue(self):
        self.assertEqual(self.source.defaultValue(1), NULL)
        self.assertEqual(self.source.defaultValue(2), "'qgis'")
        self.assertEqual(self.source.defaultValue(3), "'qgis'")

    def testValidLayerDiscoverRelationsSimple(self):
        """
        Test implicit relations that can be discovers between tables, based on declared foreign keys.
        """
        vl = QgsVectorLayer(f'{self.dbconn} table="QGIS"."REFERENCING_TABLE_SIMPLE" sql=', "test_referencing_table_simple", "oracle")
        self.assertTrue(vl.isValid())
        QgsProject.instance().addMapLayer(vl)
        vls = [
            QgsVectorLayer(f'{self.dbconn} table="QGIS"."REFERENCED_TABLE_1" sql=', "test_referenced_table_1", "oracle"),
        ]
        for lyr in vls:
            self.assertTrue(lyr.isValid())
            QgsProject.instance().addMapLayer(lyr)
        relations = vl.dataProvider().discoverRelations(vl, vls)
        self.assertEqual(len(relations), 1)
        for i, r in enumerate(relations):
            self.assertEqual(r.referencedLayer(), vls[i])

    def testValidLayerDiscoverRelationsMulti(self):
        """
        Test implicit relations that can be discovers between tables, based on declared foreign keys.
        The test also checks that three distinct relations can be discovered when three foreign keys are declared
        - two fk referencing the same layer
        - the third fk referencing another layer
        """
        vl = QgsVectorLayer(f'{self.dbconn} table="QGIS"."REFERENCING_TABLE_MULTI" sql=', "test_referencing_table_multi", "oracle")
        QgsProject.instance().addMapLayer(vl)
        self.assertTrue(vl.isValid())
        vls = [
            QgsVectorLayer(f'{self.dbconn} table="QGIS"."REFERENCED_TABLE_1" sql=', "test_referenced_table_1", "oracle"),
            QgsVectorLayer(f'{self.dbconn} table="QGIS"."REFERENCED_TABLE_2" sql=', "test_referenced_table_2", "oracle"),
            QgsVectorLayer(f'{self.dbconn} table="QGIS"."POINT_DATA" (GEOM) srid=4326 type=POINT sql=', "testpoints", "oracle")
        ]
        for lyr in vls:
            self.assertTrue(lyr.isValid())
            QgsProject.instance().addMapLayer(lyr)
        relations = vl.dataProvider().discoverRelations(vl, vls)
        self.assertEqual(len(relations), 3)

    def testValidLayerDiscoverRelationsComposite(self):
        """
        Test implicit relations that can be discovers between tables, based on composite declared foreign keys.
        """
        vl = QgsVectorLayer(f'{self.dbconn} table="QGIS"."REFERENCING_TABLE_COMPOSITE" sql=', "test_referencing_table_composite", "oracle")
        QgsProject.instance().addMapLayer(vl)
        self.assertTrue(vl.isValid())
        vls = [
            QgsVectorLayer(f'{self.dbconn} table="QGIS"."REFERENCED_TABLE_COMPOSITE" sql=', "test_referenced_table_composite", "oracle"),
            QgsVectorLayer(f'{self.dbconn} table="QGIS"."REFERENCED_TABLE_2" sql=', "test_referenced_table_2", "oracle"),
            QgsVectorLayer(f'{self.dbconn} table="QGIS"."POINT_DATA" (GEOM) srid=4326 type=POINT sql=', "testpoints", "oracle")
        ]
        for lyr in vls:
            self.assertTrue(lyr.isValid())
            QgsProject.instance().addMapLayer(lyr)
        relations = vl.dataProvider().discoverRelations(vl, vls)
        self.assertEqual(len(relations), 1)
        self.assertEqual(len(relations[0].fieldPairs()), 2)

    def testInvalidLayerDiscoverRelations(self):
        """
        Test that discover relations feature can be used on invalid layer.
        """
        vl = QgsVectorLayer(f'{self.dbconn} table="QGIS"."invalid_layer"', "invalid_layer", "oracle")
        self.assertFalse(vl.isValid())
        self.assertEqual(vl.dataProvider().discoverRelations(vl, []), [])

    def testValidLayerDiscoverRelationsNone(self):
        """
        Test checks that discover relation feature can be used on a layer that has no relation.
        """
        vl = QgsVectorLayer('%s table="QGIS"."POINT_DATA" (GEOM) srid=4326 type=POINT sql=' %
                            (self.dbconn), "testpoints", "oracle")
        self.assertTrue(vl.isValid())
        self.assertEqual(vl.dataProvider().discoverRelations(vl, []), [])

    def testPoints(self):
        vl = QgsVectorLayer('%s table="QGIS"."POINT_DATA" (GEOM) srid=4326 type=POINT sql=' %
                            (self.dbconn), "testpoints", "oracle")
        self.assertTrue(vl.isValid())

        features = [f for f in vl.getFeatures()]
        self.assertEqual(features[0].geometry().asWkt(), 'Point (1 2)')
        self.assertEqual(features[1].geometry().asWkt(), 'PointZ (1 2 3)')
        self.assertEqual(features[2].geometry().asWkt(), 'MultiPointZ ((1 2 3),(4 5 6))')
        self.assertEqual(features[3].geometry().asWkt(), 'MultiPoint ((1 2),(3 4))')
        self.assertEqual(features[4].geometry().asWkt(), 'MultiPointZ ((1 2 3),(4 5 6))')
        self.assertEqual(features[5].geometry().asWkt(), 'Point (1 2)')
        self.assertEqual(features[6].geometry().asWkt(), 'Point (3 4)')
        self.assertEqual(features[7].geometry().asWkt(), 'Point (5 6)')

    def testEditPoints(self):

        self.createTable('EDIT_POINTS_DATA', 2, 3857)
        # We choose SRID=5698 to get Oracle valid geometries because it support 3D
        self.createTable('EDIT_POINTSZ_DATA', 3, 5698)

        points = QgsVectorLayer(
            self.dbconn + ' sslmode=disable key=\'pk\' srid=3857 type=Point table="QGIS"."EDIT_POINTS_DATA" (GEOM) sql=',
            'test_lines', 'oracle')
        self.assertTrue(points.isValid())

        fid = 1
        self.check_geom(points, fid, 'Point (1 2)')

        points_z = QgsVectorLayer(
            self.dbconn + ' sslmode=disable key=\'pk\' srid=3857 type=PointZ table="QGIS"."EDIT_POINTSZ_DATA" (GEOM) sql=',
            'test_lines', 'oracle')
        self.assertTrue(points_z.isValid())

        fid += 1
        self.check_geom(points_z, fid, 'PointZ (1 2 3)')

        multipoints = QgsVectorLayer(
            self.dbconn + ' sslmode=disable key=\'pk\' srid=3857 type=MultiPoint table="QGIS"."EDIT_POINTS_DATA" (GEOM) sql=',
            'test_lines', 'oracle')
        self.assertTrue(multipoints.isValid())

        fid += 1
        self.check_geom(multipoints, fid, 'MultiPoint ((1 2),(3 4))')

        fid += 1
        self.check_geom(multipoints, fid, 'MultiPoint ((1 2))')

        multipoints_z = QgsVectorLayer(
            self.dbconn + ' sslmode=disable key=\'pk\' srid=3857 type=MultiPointZ table="QGIS"."EDIT_POINTSZ_DATA" (GEOM) sql=',
            'test_lines', 'oracle')
        self.assertTrue(multipoints_z.isValid())

        fid += 1
        self.check_geom(multipoints_z, fid, 'MultiPointZ ((1 2 7),(3 4 8))')

    def testLayerStyles(self):

        self.execSQLCommand('DROP TABLE "QGIS"."LAYER_STYLES"', True)

        # Table without geometry column
        vl_no_geom = QgsVectorLayer(
            self.dbconn + ' sslmode=disable key=\'id\' table="QGIS"."DATE_TIMES" sql=', 'test', 'oracle')

        # Save layer styles
        self.assertEqual(self.vl.saveStyleToDatabase("mystyle", "the best", True, "something.ui"), "")
        self.assertEqual(vl_no_geom.saveStyleToDatabase("my_other_style", "the very best", True, "else.ui"), "")

        # Verify presence of styles in database
        res, err = QgsProviderRegistry.instance().styleExists('oracle', self.vl.source(), 'mystyle')
        self.assertTrue(res)
        self.assertFalse(err)
        res, err = QgsProviderRegistry.instance().styleExists('oracle', vl_no_geom.source(), 'my_other_style')
        self.assertTrue(res)
        self.assertFalse(err)

        # Verify listing and loading of styles
        self.assertEqual(self.vl.listStylesInDatabase(), (1, ['0', '1'], ['mystyle', 'my_other_style'], ['the best', 'the very best'], ''))
        _, res = self.vl.loadNamedStyle(self.vl.source())
        self.assertTrue(res)
        self.assertEqual(vl_no_geom.listStylesInDatabase(), (1, ['1', '0'], ['my_other_style', 'mystyle'], ['the very best', 'the best'], ''))
        _, res = vl_no_geom.loadNamedStyle(vl_no_geom.source())
        self.assertTrue(res)

        self.execSQLCommand('DROP TABLE "QGIS"."LAYER_STYLES"')

    def testCurves(self):
        vl = QgsVectorLayer('%s table="QGIS"."LINE_DATA" (GEOM) srid=4326 type=LINESTRING sql=' %
                            (self.dbconn), "testlines", "oracle")
        self.assertTrue(vl.isValid())

        features = {f['pk']: f for f in vl.getFeatures()}
        self.assertTrue(compareWkt(features[1].geometry().asWkt(), 'LineString (1 2, 3 4, 5 6)', 0.00001), features[1].geometry().asWkt())
        self.assertTrue(compareWkt(features[2].geometry().asWkt(), 'CircularString (1 2, 5 4, 7 2.2, 10 0.1, 13 4)', 0.00001), features[2].geometry().asWkt())
        self.assertTrue(
            compareWkt(features[3].geometry().asWkt(), 'CompoundCurve ((-1 -5, 1 2),CircularString (1 2, 5 4, 7 2.20, 10 0.1, 13 4),(13 4, 17 -6))', 0.00001), features[3].geometry().asWkt())
        self.assertTrue(
            compareWkt(features[4].geometry().asWkt(), 'LineStringZ (1 2 3, 4 5 6, 7 8 9)', 0.00001), features[4].geometry().asWkt())
        self.assertTrue(
            compareWkt(features[5].geometry().asWkt(), 'MultiLineString ((1 2, 3 4),(5 6, 7 8, 9 10))', 0.00001), features[5].geometry().asWkt())
        self.assertTrue(
            compareWkt(features[6].geometry().asWkt(), 'MultiLineStringZ ((1 2 11, 3 4 -11),(5 6 9, 7 8 1, 9 10 -3))', 0.00001), features[6].geometry().asWkt())
        self.assertTrue(
            compareWkt(features[7].geometry().asWkt(), 'MultiCurve (CircularString (1 2, 5 4, 7 2.2, 10 0.1, 13 4),CircularString (-11 -3, 5 7, 10 -1))', 0.00001), features[7].geometry().asWkt())
        self.assertTrue(
            compareWkt(features[8].geometry().asWkt(), 'MultiCurve (CompoundCurve ((-1 -5, 1 2),CircularString (1 2, 5 4, 7 2.2, 10 0.1, 13 4),(13 4, 17 -6)), CircularString (1 3, 5 5, 7 3.2, 10 1.1, 13 5),LineString (-11 -3, 5 7, 10 -1))', 0.00001), features[8].geometry().asWkt())

    def check_geom(self, layer, pk, wkt, wkt_ref=None, check_valid=True):
        """
        Add geom to the layer and check everything is OK
        """
        table = layer.dataProvider().uri().table()

        # insert geom in layer
        self.assertTrue(layer.startEditing())
        feature = QgsFeature(layer.fields())
        geom = QgsGeometry.fromWkt(wkt)
        feature.setAttributes([pk])
        feature.setGeometry(geom)
        self.assertTrue(layer.addFeature(feature))
        self.assertTrue(layer.commitChanges())

        if check_valid:
            self.assertTrue(self.conn)
            query = QSqlQuery(self.conn)
            sql = f"""select p.GEOM.st_isvalid() from QGIS.{table} p where "pk" = {pk}"""
            res = query.exec(sql)
            self.assertTrue(res, sql + ': ' + query.lastError().text())
            query.next()
            valid = query.value(0)
            self.assertTrue(valid, f"geometry '{wkt}' inserted in database is not valid")
            query.finish()

        expected_wkt = wkt if wkt_ref is None else wkt_ref
        res_wkt = layer.getFeature(pk).geometry().asWkt()
        self.assertTrue(compareWkt(res_wkt, expected_wkt, 0.00001), f"\nactual   = {res_wkt}\nexpected = {expected_wkt}")

    def createTable(self, name, dims, srid):
        self.execSQLCommand(f'DROP TABLE "QGIS"."{name}"', ignore_errors=True)
        self.execSQLCommand(f"""DELETE FROM user_sdo_geom_metadata  where TABLE_NAME = '{name}'""")
        self.execSQLCommand(f"""CREATE TABLE QGIS.{name} ("pk" INTEGER PRIMARY KEY, GEOM SDO_GEOMETRY)""")
        self.execSQLCommand("""INSERT INTO user_sdo_geom_metadata (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID) VALUES ( '{}', 'GEOM', sdo_dim_array(sdo_dim_element('X',-50,50,0.005),sdo_dim_element('Y',-50,50,0.005){}),{})""".format(
            name, ",sdo_dim_element('Z',-50,50,0.005)" if dims > 2 else "", srid), ignore_errors=True)
        self.execSQLCommand("""CREATE INDEX {0}_spatial_idx ON QGIS.{0}(GEOM) INDEXTYPE IS MDSYS.SPATIAL_INDEX""".format(name))

    def testEditCurves(self):

        self.createTable('EDIT_CURVE_DATA', 2, 3857)
        # We choose SRID=5698 (see https://docs.oracle.com/database/121/SPATL/three-dimensional-coordinate-reference-system-support.htm#SPATL626)
        # to get Oracle valid geometries because it support 3D and arcs (arcs are not supported in geodetic projection)
        self.createTable('EDIT_CURVEZ_DATA', 3, 5698)

        lines = QgsVectorLayer(
            self.dbconn + ' sslmode=disable key=\'pk\' srid=3857 type=LineString table="QGIS"."EDIT_CURVE_DATA" (GEOM) sql=',
            'test_lines', 'oracle')
        self.assertTrue(lines.isValid())

        fid = 1
        self.check_geom(lines, fid, 'LineString (1 2, 3 4, 5 6)')

        fid += 1
        self.check_geom(lines, fid, 'CircularString (1 2, 5 4, 7 2.2, 10 0.1, 13 4)')

        fid += 1
        self.check_geom(lines, fid, 'CompoundCurve ((-1 -5, 1 2),CircularString (1 2, 5 4, 7 2.20, 10 0.1, 13 4),(13 4, 17 -6))')

        # We choose SRID=5698 (see https://docs.oracle.com/database/121/SPATL/three-dimensional-coordinate-reference-system-support.htm#SPATL626)
        # to get Oracle valid geometries because it support 3D and arcs (arcs are not supported in geodetic projection)
        lines_z = QgsVectorLayer(
            self.dbconn + ' sslmode=disable key=\'pk\' srid=5698 type=LineStringZ table="QGIS"."EDIT_CURVEZ_DATA" (GEOM) sql=',
            'test_lines', 'oracle')
        self.assertTrue(lines_z.isValid())

        fid += 1
        self.check_geom(lines_z, fid, 'LineStringZ (1 2 3, 4 5 6, 7 8 9)')
        # 3D arcs and compound curve are invalid
        # https://support.oracle.com/knowledge/Oracle%20Database%20Products/1446335_1.html
        # https://support.oracle.com/knowledge/Oracle%20Database%20Products/1641672_1.html

        fid += 1
        self.check_geom(lines_z, fid, 'CircularStringZ (1 2 1, 5 4 2, 7 2.2 3, 10 0.1 4, 13 4 5)', check_valid=False)

        fid += 1
        self.check_geom(lines_z, fid, 'CompoundCurveZ ((-1 -5 1, 1 2 2),CircularStringZ (1 2 2, 5 4 3, 7 2.20 4, 10 0.1 5, 13 4 6),(13 4 6, 17 -6 7))', check_valid=False)

        multi_lines = QgsVectorLayer(
            self.dbconn + ' sslmode=disable key=\'pk\' srid=3857 type=MultiLineString table="QGIS"."EDIT_CURVE_DATA" (GEOM) sql=',
            'test_multilines', 'oracle')
        self.assertTrue(multi_lines.isValid())

        fid += 1
        self.check_geom(multi_lines, fid, 'MultiLineString ((1 2, 3 4),(5 6, 7 8, 9 10), (11 12, 13 14))')

        fid += 1
        self.check_geom(multi_lines, fid, 'MultiLineString ((1 2, 3 4),(5 6, 7 8, 9 10))')

        fid += 1
        self.check_geom(multi_lines, fid, 'MultiLineString ((1 2, 3 4))')

        multi_lines_z = QgsVectorLayer(
            self.dbconn + ' sslmode=disable key=\'pk\' srid=5698 type=MultiLineStringZ table="QGIS"."EDIT_CURVEZ_DATA" (GEOM) sql=',
            'test_multilines', 'oracle')
        self.assertTrue(multi_lines_z.isValid())

        fid += 1
        self.check_geom(multi_lines_z, fid, 'MultiLineStringZ ((1 2 11, 3 4 -11),(5 6 9, 7 8 1, 9 10 -3))')

        fid += 1
        self.check_geom(multi_lines_z, fid, 'MultiLineStringZ ((1 2 1, 3 4 2),(5 6 3, 7 8 4, 9 10 5), (11 12 6, 13 14 7))')

        fid += 1
        self.check_geom(multi_lines_z, fid, 'MultiLineStringZ ((1 2 1, 3 4 2))')

        multi_curves = QgsVectorLayer(
            self.dbconn + ' sslmode=disable key=\'pk\' srid=3857 type=MultiCurve table="QGIS"."EDIT_CURVE_DATA" (GEOM) sql=',
            'test_multicurves', 'oracle')
        self.assertTrue(multi_curves.isValid())

        # There is no way to represent a compound curve with only one LineString or CircularString in Oracle database
        # if SDO_ETYPE = 4, n must be greater than 1 : https://docs.oracle.com/database/121/SPATL/sdo_geometry-object-type.htm#GUID-270AE39D-7B83-46D0-9DD6-E5D99C045021__BGHDGCCE
        # So, this two different WKTs inputs generate the same data in Oracle database, and so the same WKT
        # output representation (with CompoundCurve() around each MultiCurve parts)
        fid += 1
        self.check_geom(multi_curves, fid,
                        'MultiCurve (CompoundCurve ((-1 -5, 1 2),CircularString (1 2, 5 4, 7 2.2, 10 0.1, 13 4),(13 4, 17 -6)),CircularString (1 3, 5 5, 7 3.2, 10 1.1, 13 5),LineString (-11 -3, 5 7, 10 -1))')
        fid += 1
        self.check_geom(multi_curves, fid,
                        'MultiCurve (CompoundCurve ((-1 -5, 1 2),CircularString (1 2, 5 4, 7 2.2, 10 0.1, 13 4),(13 4, 17 -6)),CompoundCurve (CircularString (1 3, 5 5, 7 3.2, 10 1.1, 13 5)),(-11 -3, 5 7, 10 -1))',
                        'MultiCurve (CompoundCurve ((-1 -5, 1 2),CircularString (1 2, 5 4, 7 2.2, 10 0.1, 13 4),(13 4, 17 -6)),CircularString (1 3, 5 5, 7 3.2, 10 1.1, 13 5),LineString (-11 -3, 5 7, 10 -1))')
        fid += 1
        self.check_geom(multi_curves, fid,
                        'MultiCurve (CompoundCurve ((-1 -5, 1 2),(1 2, 17 -6)),CircularString (1 2, 5 4, 7 2.2, 10 0.1, 13 4))')

        fid += 1
        self.check_geom(multi_curves, fid,
                        'MultiCurve (CompoundCurve ((-1 -5, 1 2),(1 2, 17 -6)))')

        multi_curves_z = QgsVectorLayer(
            self.dbconn + ' sslmode=disable key=\'pk\' srid=5698 type=MultiCurveZ table="QGIS"."EDIT_CURVEZ_DATA" (GEOM) sql=',
            'test_multicurves_z', 'oracle')
        self.assertTrue(multi_curves_z.isValid())

        # ora-54530 : 3D compound lines are invalid since 11.2.0.3 : https://support.oracle.com/knowledge/Oracle%20Database%20Products/1446335_1.html
        fid += 1
        self.check_geom(multi_curves_z, fid,
                        'MultiCurveZ (CompoundCurveZ ((-1 -5 3, 1 2 4),CircularStringZ (1 2 4, 5 4 5, 7 2.2 6, 10 0.1 7, 13 4 8),(13 4 8, 17 -6 9)), CircularStringZ (1 3 2, 5 5 3, 7 3.2 4, 10 1.1 5, 13 5 6),LineStringZ (-11 -3 1, 5 7 2, 10 -1 3))',
                        check_valid=False)
        fid += 1
        self.check_geom(multi_curves_z, fid,
                        'MultiCurveZ (CompoundCurveZ ((-1 -5 3, 1 2 4),CircularStringZ (1 2 4, 5 4 5, 7 2.2 6, 10 0.1 7, 13 4 8),(13 4 8, 17 -6 9)),CompoundCurveZ (CircularStringZ (1 3 2, 5 5 3, 7 3.2 4, 10 1.1 5, 13 5 6)),(-11 -3 1, 5 7 2, 10 -1 3))',
                        'MultiCurveZ (CompoundCurveZ ((-1 -5 3, 1 2 4),CircularStringZ (1 2 4, 5 4 5, 7 2.2 6, 10 0.1 7, 13 4 8),(13 4 8, 17 -6 9)), CircularStringZ (1 3 2, 5 5 3, 7 3.2 4, 10 1.1 5, 13 5 6),LineStringZ (-11 -3 1, 5 7 2, 10 -1 3))',
                        check_valid=False)

        fid += 1
        self.check_geom(multi_curves_z, fid,
                        'MultiCurveZ (CompoundCurveZ ((-1 -5 3, 1 2 4),(1 2 4, 17 -6 8)))',
                        check_valid=False)

    def testSurfaces(self):
        vl = QgsVectorLayer('%s table="QGIS"."POLY_DATA" (GEOM) srid=4326 type=POLYGON sql=' %
                            (self.dbconn), "testpoly", "oracle")
        self.assertTrue(vl.isValid())

        features = {f['pk']: f for f in vl.getFeatures()}
        self.assertTrue(compareWkt(features[1].geometry().asWkt(), 'Polygon ((1 2, 11 2, 11 22, 1 22, 1 2))', 0.00001), features[1].geometry().asWkt())
        self.assertTrue(compareWkt(features[2].geometry().asWkt(), 'PolygonZ ((1 2 3, 11 2 13, 11 22 15, 1 22 7, 1 2 3))', 0.00001), features[2].geometry().asWkt())
        self.assertTrue(
            compareWkt(features[3].geometry().asWkt(), 'Polygon ((1 2, 11 2, 11 22, 1 22, 1 2),(5 6, 8 9, 8 6, 5 6),(3 4, 5 6, 3 6, 3 4))', 0.00001), features[3].geometry().asWkt())
        self.assertTrue(
            compareWkt(features[4].geometry().asWkt(), 'PolygonZ ((1 2 3, 11 2 13, 11 22 15, 1 22 7, 1 2 3),(5 6 1, 8 9 -1, 8 6 2, 5 6 1))', 0.00001), features[4].geometry().asWkt())
        self.assertTrue(
            compareWkt(features[5].geometry().asWkt(), 'Polygon ((1 2, 11 2, 11 22, 1 22, 1 2))', 0.00001), features[5].geometry().asWkt())
        self.assertTrue(
            compareWkt(features[6].geometry().asWkt(), 'CurvePolygon (CircularString (6.76923076923076916 22.82875364393326834, 17.98259979777942519 11.61538461538461497, 6.76923076923076916 0.40201558683595984, -4.44413825931788598 11.61538461538461497, 6.76923076923076916 22.82875364393326834))', 0.00001), features[6].geometry().asWkt())
        self.assertTrue(
            compareWkt(features[7].geometry().asWkt(), 'MultiPolygon (((1 2, 11 2, 11 22, 1 22, 1 2)),((1 2, 11 2, 11 22, 1 22, 1 2),(5 6, 8 9, 8 6, 5 6),(3 4, 5 6, 3 6, 3 4)))', 0.00001), features[7].geometry().asWkt())
        self.assertTrue(
            compareWkt(features[8].geometry().asWkt(), 'MultiPolygonZ (((1 2 3, 11 2 13, 11 22 15, 1 22 7, 1 2 3)),((1 2 3, 11 2 13, 11 22 15, 1 22 7, 1 2 3),(5 6 1, 8 9 -1, 8 6 2, 5 6 1)))', 0.00001), features[8].geometry().asWkt())
        self.assertTrue(
            compareWkt(features[9].geometry().asWkt(), 'CurvePolygon (CircularString (1 3, 3 5, 4 7, 7 3, 1 3))', 0.00001), features[9].geometry().asWkt())
        self.assertTrue(
            compareWkt(features[10].geometry().asWkt(), 'CurvePolygon (CircularString (1 3, 3 5, 4 7, 7 3, 1 3),CircularString (3.1 3.3, 3.3 3.5, 3.4 3.7, 3.7 3.3, 3.1 3.3))', 0.00001), features[10].geometry().asWkt())
        self.assertTrue(
            compareWkt(features[11].geometry().asWkt(), 'CurvePolygon(CompoundCurve ((-1 -5, 1 2),CircularString (1 2, 5 4, 7 2.20, 10 0.1, 13 4),(13 4, 17 -6),CircularString (17 -6, 5 -7, -1 -5)))', 0.00001), features[11].geometry().asWkt())
        self.assertTrue(
            compareWkt(features[12].geometry().asWkt(), 'MultiSurface (CurvePolygon (CircularString (1 3, 3 5, 4 7, 7 3, 1 3)),CurvePolygon (CircularString (11 3, 13 5, 14 7, 17 3, 11 3)))', 0.00001), features[12].geometry().asWkt())
        self.assertTrue(
            compareWkt(features[13].geometry().asWkt(), 'CurvePolygonZ(CompoundCurveZ (CircularStringZ (-1 -5 1, 5 -7 2, 17 -6 3), (17 -6 3, 13 4 4), CircularStringZ (13 4 4, 10 0.1 5, 7 2.20 6, 5 4 7, 1 2 8),(1 2 8, -1 -5 1)))', 0.00001), features[13].geometry().asWkt())
        self.assertTrue(
            compareWkt(features[14].geometry().asWkt(), 'MultiPolygon (((22 22, 28 22, 28 26, 22 26, 22 22)))', 0.00001), features[14].geometry().asWkt())
        self.assertTrue(
            compareWkt(features[15].geometry().asWkt(), 'MultiSurface (CurvePolygon(CompoundCurve (CircularString (-1 -5, 5 -7, 17 -6), (17 -6, -1 -5))), CurvePolygon (CircularString (1 3, 7 3, 4 7, 3 5, 1 3)))', 0.00001), features[15].geometry().asWkt())

    def testEditSurfaces(self):

        self.createTable('EDIT_SURFACE_DATA', 2, 3857)
        # We choose SRID=5698 (see https://docs.oracle.com/database/121/SPATL/three-dimensional-coordinate-reference-system-support.htm#SPATL626)
        # to get Oracle valid geometries because it support 3D and arcs (arcs are not supported in geodetic projection)
        self.createTable('EDIT_SURFACEZ_DATA', 3, 5698)

        polygon = QgsVectorLayer(
            self.dbconn + ' sslmode=disable key=\'pk\' srid=3857 type=Polygon table="QGIS"."EDIT_SURFACE_DATA" (GEOM) sql=',
            'test_polygon', 'oracle')
        self.assertTrue(polygon.isValid())

        fid = 1
        self.check_geom(polygon, fid, 'Polygon ((1 2, 11 2, 11 22, 1 22, 1 2))')
        fid += 1
        self.check_geom(polygon, fid, 'Polygon ((1 2, 11 2, 11 22, 1 22, 1 2),(5 6, 8 9, 8 6, 5 6),(3 4, 3 6, 5 6, 3 4))')
        fid += 1
        # Outer ring in clockwise order --> reversed on writing
        self.check_geom(polygon, fid, 'Polygon ((0 0, 0 1, 1 1, 0 0))', 'Polygon ((0 0, 1 1, 0 1, 0 0))')
        fid += 1
        # Outer ring in clockwise order --> reversed on writing. Inner ring in clockwise order --> unmodified
        self.check_geom(polygon, fid, 'Polygon ((0 0, 0 1, 1 1, 0 0),(0.1 0.2, 0.1 0.9, 0.7 0.9, 0.1 0.2))', 'Polygon ((0 0, 1 1, 0 1, 0 0),(0.1 0.2, 0.1 0.9, 0.7 0.9, 0.1 0.2))')
        fid += 1
        # Inner ring in counterclockwise order --> reversed on writing. Outer ring in counterclockwise order --> unmodified
        self.check_geom(polygon, fid, 'Polygon ((0 0, 1 1, 0 1, 0 0),(0.1 0.2, 0.7 0.9, 0.1 0.9, 0.1 0.2))', 'Polygon ((0 0, 1 1, 0 1, 0 0),(0.1 0.2, 0.1 0.9, 0.7 0.9, 0.1 0.2))')
        fid += 1

        polygon_z = QgsVectorLayer(
            self.dbconn + ' sslmode=disable key=\'pk\' srid=5698 type=PolygonZ table="QGIS"."EDIT_SURFACEZ_DATA" (GEOM) sql=',
            'test_polygon_z', 'oracle')
        self.assertTrue(polygon_z.isValid())

        # non planar polygon are invalid : to http://ora.codes/ora-54505/
        self.check_geom(polygon_z, fid, 'PolygonZ ((1 2 3, 11 2 3, 11 22 3, 1 22 3, 1 2 3))')
        fid += 1
        self.check_geom(polygon_z, fid, 'PolygonZ ((1 2 4, 11 2 5, 11 22 6, 1 22 7, 1 2 1))', check_valid=False)
        fid += 1
        self.check_geom(polygon_z, fid, 'PolygonZ ((1 2 3, 11 2 3, 11 22 3, 1 22 3, 1 2 3),(5 6 3, 8 9 3, 8 6 3, 5 6 3))')
        fid += 1
        self.check_geom(polygon_z, fid, 'PolygonZ ((1 2 3, 11 2 13, 11 22 15, 1 22 7, 1 2 3),(5 6 1, 8 9 -1, 8 6 2, 5 6 1))', check_valid=False)
        fid += 1

        multi_polygon = QgsVectorLayer(
            self.dbconn + ' sslmode=disable key=\'pk\' srid=3857 type=MultiPolygon table="QGIS"."EDIT_SURFACE_DATA" (GEOM) sql=',
            'multi_polygon', 'oracle')
        self.assertTrue(multi_polygon.isValid())

        self.check_geom(multi_polygon, fid, 'MultiPolygon (((22 22, 28 22, 28 26, 22 26, 22 22)),((1 2, 11 2, 11 22, 1 22, 1 2),(5 6, 8 9, 8 6, 5 6),(3 4, 3 6, 5 6, 3 4)))')
        fid += 1
        self.check_geom(multi_polygon, fid, 'MultiPolygon (((22 22, 28 22, 28 26, 22 26, 22 22)))')
        fid += 1

        multi_polygon_z = QgsVectorLayer(
            self.dbconn + ' sslmode=disable key=\'pk\' srid=5698 type=MultiPolygonZ table="QGIS"."EDIT_SURFACEZ_DATA" (GEOM) sql=',
            'multi_polygon_z', 'oracle')
        self.assertTrue(multi_polygon_z.isValid())

        self.check_geom(multi_polygon_z, fid, 'MultiPolygonZ (((22 22 1, 28 22 2, 28 26 3, 22 26 4, 22 22 1)),((1 2 3, 11 2 4, 11 22 5, 1 22 6, 1 2 3),(5 6 1, 8 9 2, 8 6 3, 5 6 1),(3 4 0, 3 6 1, 5 6 2, 3 4 0)))', check_valid=False)
        fid += 1

        curve_polygon = QgsVectorLayer(
            self.dbconn + ' sslmode=disable key=\'pk\' srid=3857 type=CurvePolygon table="QGIS"."EDIT_SURFACE_DATA" (GEOM) sql=',
            'curve_polygon', 'oracle')
        self.assertTrue(curve_polygon.isValid())

        self.check_geom(curve_polygon, fid, 'CurvePolygon (CircularString (6.76923076923076916 22.82875364393326834, -4.44413825931788598 11.61538461538461497, 6.76923076923076916 0.40201558683595984, 17.98259979777942519 11.61538461538461497, 6.76923076923076916 22.82875364393326834))')
        fid += 1
        self.check_geom(curve_polygon, fid, 'CurvePolygon (CircularString (1 3, 7 3, 4 7, 3 5, 1 3))')
        fid += 1
        self.check_geom(curve_polygon, fid, 'CurvePolygon (CircularString (1 3, 7 3, 4 7, 3 5, 1 3),CircularString (3.1 3.3, 3.3 3.5, 3.4 3.7, 3.7 3.3, 3.1 3.3))')
        fid += 1
        self.check_geom(curve_polygon, fid, 'CurvePolygon(CompoundCurve (CircularString (-1 -5, 5 -7, 17 -6), (17 -6, 13 4), CircularString (13 4, 10 0.1, 7 2.20, 5 4, 1 2),(1 2, -1 -5)))')
        fid += 1
        self.check_geom(curve_polygon, fid, 'CurvePolygon(CircularString (0 0, 30 0, 30 20, 0 30, 0 0), CompoundCurve ((13 10, 17 2), CircularString (17 2, 1 1, 13 10)))')
        fid += 1
        self.check_geom(curve_polygon, fid, 'CurvePolygon(CircularString (0 0, 30 0, 30 20, 0 30, 0 0), (13 10, 17 2, 1 1, 13 10))')
        fid += 1
        self.check_geom(curve_polygon, fid, 'CurvePolygon((0 0, 30 0, 30 20, 0 30, 0 0), CircularString (1 3, 3 5, 4 7, 7 3, 1 3))')
        fid += 1
        # Reverse orientation of outer ring
        self.check_geom(curve_polygon, fid, 'CurvePolygon((0 0, 0 30, 30 20, 30 0, 0 0), CircularString (1 3, 3 5, 4 7, 7 3, 1 3))', 'CurvePolygon((0 0, 30 0, 30 20, 0 30, 0 0), CircularString (1 3, 3 5, 4 7, 7 3, 1 3))')
        fid += 1
        # Reverse orientation of outer ring
        self.check_geom(curve_polygon, fid, 'CurvePolygon( CompoundCurve ((0 0, 0 1, 1 1),(1 1,0 0)))', 'CurvePolygon (CompoundCurve ((0 0, 1 1),(1 1, 0 1, 0 0)))')
        fid += 1

        curve_polygon_z = QgsVectorLayer(
            self.dbconn + ' sslmode=disable key=\'pk\' srid=5698 type=CurvePolygonZ table="QGIS"."EDIT_SURFACEZ_DATA" (GEOM) sql=',
            'curve_polygon_z', 'oracle')
        self.assertTrue(curve_polygon_z.isValid())

        # There is no way to build a valid 3D curve polygon (even with make3d from the valid 2D one)
        # we get ora-13033 although everything is OK in sdo_elem_array ...
        self.check_geom(curve_polygon_z, fid, 'CurvePolygonZ (CircularStringZ (6.76923076923076916 22.82875364393326834 1, -4.44413825931788598 11.61538461538461497 2, 6.76923076923076916 0.40201558683595984 3, 17.98259979777942519 11.61538461538461497 4, 6.76923076923076916 22.82875364393326834 1))', check_valid=False)
        fid += 1
        self.check_geom(curve_polygon_z, fid, 'CurvePolygonZ (CircularStringZ (1 3 1, 7 3 2, 4 7 3, 3 5 2, 1 3 1))', check_valid=False)
        fid += 1
        self.check_geom(curve_polygon_z, fid, 'CurvePolygonZ (CircularStringZ (1 3 1, 7 3 2, 4 7 3, 3 5 4, 1 3 1),CircularStringZ (3.1 3.3 1, 3.3 3.5 2, 3.4 3.7 3, 3.7 3.3 4, 3.1 3.3 1))', check_valid=False)
        fid += 1
        self.check_geom(curve_polygon_z, fid, 'CurvePolygonZ(CompoundCurveZ (CircularStringZ (-1 -5 1, 5 -7 2, 17 -6 3), (17 -6 3, 13 4 4), CircularStringZ (13 4 4, 10 0.1 5, 7 2.20 6, 5 4 7, 1 2 8),(1 2 8, -1 -5 1)))', check_valid=False)
        fid += 1
        self.check_geom(curve_polygon_z, fid, 'CurvePolygonZ(CircularStringZ (0 0 1, 30 0 2, 30 20 3, 0 30 4, 0 0 5), CompoundCurveZ ((13 10 1, 17 2 3), CircularStringZ (17 2 3, 1 1 5, 13 10 1)))', check_valid=False)
        fid += 1

        multi_surface = QgsVectorLayer(
            self.dbconn + ' sslmode=disable key=\'pk\' srid=3857 type=MultiSurface table="QGIS"."EDIT_SURFACE_DATA" (GEOM) sql=',
            'multi_surface', 'oracle')
        self.assertTrue(multi_surface.isValid())

        self.check_geom(multi_surface, fid, 'MultiSurface (CurvePolygon (CircularString (1 3, 7 3, 4 7, 3 5, 1 3)),CurvePolygon (CircularString (11 3, 17 3, 14 7, 13 5, 11 3)))')
        fid += 1
        self.check_geom(multi_surface, fid, 'MultiSurface (CurvePolygon (CircularString (1 3, 7 3, 4 7, 3 5, 1 3)))')
        fid += 1
        self.check_geom(multi_surface, fid, 'MultiSurface (CurvePolygon(CompoundCurve (CircularString (-1 -5, 5 -7, 17 -6), (17 -6, -1 -5))), CurvePolygon (CircularString (1 3, 7 3, 4 7, 3 5, 1 3)))')
        fid += 1
        wkt = 'MultiSurface (((0 0, 1 0, 1 1, 0 0)),((100 100, 101 100, 101 101, 100 100)))'
        self.check_geom(multi_surface, fid, wkt, wkt.replace('MultiSurface', 'MultiPolygon'))
        fid += 1

        # Outer ring in clockwise order --> reversed on writing
        self.check_geom(multi_surface, fid, 'MultiSurface( Polygon ((0 0, 0 1, 1 1, 0 0)))', 'MultiPolygon (((0 0, 1 1, 0 1, 0 0)))')
        fid += 1
        # Outer ring in clockwise order --> reversed on writing. Inner ring in clockwise order --> unmodified
        self.check_geom(multi_surface, fid, 'MultiSurface(Polygon ((0 0, 0 1, 1 1, 0 0),(0.1 0.2, 0.1 0.9, 0.7 0.9, 0.1 0.2)))', 'MultiPolygon (((0 0, 1 1, 0 1, 0 0),(0.1 0.2, 0.1 0.9, 0.7 0.9, 0.1 0.2)))')
        fid += 1
        # Inner ring in counterclockwise order --> reversed on writing. Outer ring in counterclockwise order --> unmodified
        self.check_geom(multi_surface, fid, 'MultiSurface(Polygon ((0 0, 1 1, 0 1, 0 0),(0.1 0.2, 0.7 0.9, 0.1 0.9, 0.1 0.2)))', 'MultiPolygon (((0 0, 1 1, 0 1, 0 0),(0.1 0.2, 0.1 0.9, 0.7 0.9, 0.1 0.2)))')
        fid += 1

        self.check_geom(multi_surface, fid, 'MultiSurface (Polygon ((0 0, 1 0, 1 1, 0 0)),CurvePolygon (CompoundCurve (CircularString (100 100, 105.5 99.5, 101 100, 101.5 100.5, 101 101),(101 101, 100 100))))')
        fid += 1
        self.check_geom(multi_surface, fid, 'MultiSurface (CurvePolygon (CompoundCurve (CircularString (100 100, 101 100, 101 101),(101 101, 100 100))),Polygon ((0 0, 1 0, 1 1, 0 0)))')
        fid += 1
        self.check_geom(multi_surface, fid, 'MultiSurface(Polygon((100 100, 101 100, 101 101, 100 100)), CurvePolygon((0 0, 30 0, 30 20, 0 30, 0 0), CircularString (1 3, 3 5, 4 7, 7 3, 1 3)))')
        fid += 1

        multi_surface_z = QgsVectorLayer(
            self.dbconn + ' sslmode=disable key=\'pk\' srid=5698 type=MultiSurfaceZ table="QGIS"."EDIT_SURFACEZ_DATA" (GEOM) sql=',
            'multi_surface_z', 'oracle')
        self.assertTrue(multi_surface_z.isValid())

        # ora-54530 : 3D compound lines are invalid since 11.2.0.3 : https://support.oracle.com/knowledge/Oracle%20Database%20Products/1446335_1.html
        self.check_geom(multi_surface_z, fid, 'MultiSurfaceZ (CurvePolygonZ (CircularStringZ (1 3 1, 7 3 2, 4 7 3, 3 5 4, 1 3 1)),CurvePolygonZ (CircularStringZ (11 3 1, 17 3 2, 14 7 3, 13 5 4, 11 3 1)))', check_valid=False)
        fid += 1
        self.check_geom(multi_surface_z, fid, 'MultiSurfaceZ (CurvePolygonZ (CircularStringZ (1 3 1, 7 3 2, 4 7 3, 3 5 4, 1 3 1)))', check_valid=False)
        fid += 1
        self.check_geom(multi_surface_z, fid, 'MultiSurfaceZ (CurvePolygonZ(CompoundCurveZ (CircularStringZ (-1 -5 1, 5 -7 2, 17 -6 3), (17 -6 3, -1 -5 1))), CurvePolygonZ (CircularStringZ (1 3 1, 7 3 2, 4 7 3, 3 5 4, 1 3 1)))', check_valid=False)
        fid += 1

    def testFeatureCount(self):
        self.execSQLCommand('CREATE OR REPLACE VIEW QGIS.VIEW_POLY_DATA AS SELECT * FROM QGIS.POLY_DATA')
        self.execSQLCommand("BEGIN DBMS_STATS.GATHER_TABLE_STATS('QGIS', 'SOME_DATA'); END;")

        view_layer = QgsVectorLayer(self.dbconn + ' sslmode=disable table="QGIS"."VIEW_POLY_DATA" key=\'pk\'',
                                    'test', 'oracle')
        self.assertTrue(view_layer.isValid())
        self.assertGreater(view_layer.featureCount(), 0)
        self.assertTrue(view_layer.setSubsetString('"pk" = 5'))
        self.assertGreaterEqual(view_layer.featureCount(), 0)

        view_layer_estimated = QgsVectorLayer(self.dbconn + ' sslmode=disable estimatedmetadata=true table="QGIS"."VIEW_POLY_DATA" key=\'pk\'',
                                              'test', 'oracle')
        self.assertTrue(view_layer_estimated.isValid())
        self.assertGreater(view_layer_estimated.featureCount(), 0)
        self.assertTrue(view_layer_estimated.setSubsetString('"pk" = 5'))
        self.assertGreaterEqual(view_layer_estimated.featureCount(), 0)

        self.assertGreater(self.vl.featureCount(), 0)
        self.assertTrue(self.vl.setSubsetString('"pk" = 3'))
        self.assertGreaterEqual(self.vl.featureCount(), 1)
        self.assertTrue(self.vl.setSubsetString(''))

        vl_estimated = QgsVectorLayer(self.dbconn + ' sslmode=disable estimatedmetadata=true table="QGIS"."SOME_DATA"',
                                      'test', 'oracle')
        self.assertTrue(vl_estimated.isValid())
        self.assertGreater(vl_estimated.featureCount(), 0)
        self.assertTrue(vl_estimated.setSubsetString('"pk" = 3'))
        self.assertGreaterEqual(vl_estimated.featureCount(), 1)

        self.execSQLCommand('DROP VIEW QGIS.VIEW_POLY_DATA')

    def testNestedInsert(self):
        tg = QgsTransactionGroup()
        tg.addLayer(self.vl)
        self.vl.startEditing()
        it = self.vl.getFeatures()
        f = next(it)
        f['pk'] = NULL
        self.vl.addFeature(f)  # Should not deadlock during an active iteration
        f = next(it)
        self.vl.rollBack()

    def testTransactionEditing(self):
        tg = QgsTransactionGroup()
        tg.addLayer(self.vl)
        self.vl.startEditing()
        feat = QgsFeature(self.vl.fields())
        feat.setAttribute("pk", 6)
        self.assertTrue(self.vl.addFeature(feat))
        self.vl.rollBack()

    def testTimeout(self):
        """
        Asserts that we will not deadlock if more iterators are opened in parallel than
        available in the connection pool
        """
        request = QgsFeatureRequest()
        request.setTimeout(1)

        iterators = list()
        for i in range(100):
            iterators.append(self.vl.getFeatures(request))

    def testTransactionDirtyName(self):
        # create a vector layer based on oracle
        vl = QgsVectorLayer(
            self.dbconn + ' sslmode=disable key=\'pk\' srid=4326 type=POLYGON table="QGIS"."SOME_POLY_DATA" (GEOM) sql=', 'test', 'oracle')
        self.assertTrue(vl.isValid())

        # prepare a project with transactions enabled
        p = QgsProject()
        p.setTransactionMode(Qgis.TransactionMode.AutomaticGroups)
        p.addMapLayers([vl])
        vl.startEditing()

        # update the data within the transaction
        tr = vl.dataProvider().transaction()
        sql = """UPDATE "QGIS"."SOME_POLY_DATA" SET "pk"=1 where "pk"=1"""
        name = "My Awesome Transaction!"
        self.assertTrue(tr.executeSql(sql, True, name)[0])

        # test name
        self.assertEqual(vl.undoStack().command(0).text(), name)

        # rollback
        vl.rollBack()

    def testTransactionDirty(self):
        # create a vector layer based on oracle
        vl = QgsVectorLayer(
            self.dbconn + ' sslmode=disable key=\'pk\' srid=4326 type=POLYGON table="QGIS"."SOME_POLY_DATA" (GEOM) sql=', 'test', 'oracle')
        self.assertTrue(vl.isValid())

        # prepare a project with transactions enabled
        p = QgsProject()
        p.setTransactionMode(Qgis.TransactionMode.AutomaticGroups)
        p.addMapLayers([vl])
        vl.startEditing()

        # check that the feature used for testing is ok
        ft0 = vl.getFeatures('pk=1')
        f = QgsFeature()
        self.assertTrue(ft0.nextFeature(f))

        # update the data within the transaction
        tr = vl.dataProvider().transaction()
        sql = """UPDATE "QGIS"."SOME_POLY_DATA" set "pk"=33 where "pk"=1"""
        self.assertTrue(tr.executeSql(sql, True)[0])

        # check that the pk of the feature has been changed
        ft = vl.getFeatures('pk=1')
        self.assertFalse(ft.nextFeature(f))

        ft = vl.getFeatures('pk=33')
        self.assertTrue(ft.nextFeature(f))

        # underlying data has been modified but the layer is not tagged as
        # modified
        self.assertTrue(vl.isModified())

        # undo sql query
        vl.undoStack().undo()

        # check that the original feature with pk is back
        ft0 = vl.getFeatures('pk=1')
        self.assertTrue(ft0.nextFeature(f))

        # redo
        vl.undoStack().redo()

        # check that the pk of the feature has been changed
        ft1 = vl.getFeatures('pk=1')
        self.assertFalse(ft1.nextFeature(f))

        # rollback
        vl.rollBack()

    def testTransactionTuple(self):
        # create a vector layer based on oracle
        vl = QgsVectorLayer(
            self.dbconn + ' sslmode=disable key=\'pk\' srid=4326 type=POLYGON table="QGIS"."SOME_POLY_DATA" (GEOM) sql=',
            'test', 'oracle')
        self.assertTrue(vl.isValid())

        # prepare a project with transactions enabled
        p = QgsProject()
        p.setTransactionMode(Qgis.TransactionMode.AutomaticGroups)
        p.addMapLayers([vl])
        vl.startEditing()

        # execute a query which returns a tuple
        tr = vl.dataProvider().transaction()
        sql = 'SELECT * from "QGIS"."SOME_POLY_DATA"'
        self.assertTrue(tr.executeSql(sql, False)[0])

        # underlying data has not been modified
        self.assertFalse(vl.isModified())

    def testIdentityCommit(self):
        # create a vector layer based on oracle
        vl = QgsVectorLayer(
            self.dbconn + ' sslmode=disable key=\'pk\' srid=4326 type=POINT table="QGIS"."POINT_DATA_IDENTITY" (GEOM) sql=',
            'test', 'oracle')
        self.assertTrue(vl.isValid())

        features = [f for f in vl.getFeatures()]

        # add a new feature
        newf = QgsFeature(features[0].fields())
        success, featureAdded = vl.dataProvider().addFeatures([newf])
        self.assertTrue(success)

        # clean up
        features = [f for f in vl.getFeatures()]
        vl.dataProvider().deleteFeatures([features[-1].id()])

    def testGetFeatureFidInvalid(self):
        """
        Get feature with an invalid fid
        https://github.com/qgis/QGIS/issues/31626
        """
        self.execSQLCommand('DROP TABLE "QGIS"."TABLE_QGIS_ISSUE_FLOAT_KEY"', ignore_errors=True)
        self.execSQLCommand("""CREATE TABLE "QGIS"."TABLE_QGIS_ISSUE_FLOAT_KEY" (CODE NUMBER PRIMARY KEY, DESCRIPTION VARCHAR2(25))""")
        self.execSQLCommand("""INSERT INTO "QGIS"."TABLE_QGIS_ISSUE_FLOAT_KEY" VALUES(1000,'Desc for 1st record')""")
        self.execSQLCommand("""INSERT INTO "QGIS"."TABLE_QGIS_ISSUE_FLOAT_KEY" VALUES(2000,'Desc for 2nd record')""")
        self.execSQLCommand("""CREATE OR REPLACE VIEW "QGIS"."VIEW_QGIS_ISSUE_FLOAT_KEY" AS SELECT * FROM "QGIS"."TABLE_QGIS_ISSUE_FLOAT_KEY" """)

        vl = QgsVectorLayer(
            self.dbconn + ' sslmode=disable key=\'CODE\' table="QGIS"."VIEW_QGIS_ISSUE_FLOAT_KEY" sql=',
            'test', 'oracle')

        # feature are not loaded yet, mapping between CODE and fid is not built so feature
        # is invalid
        feature = vl.getFeature(2)
        self.assertTrue(not feature.isValid())

        # load all features
        for f in vl.getFeatures():
            pass

        feature = vl.getFeature(2)
        self.assertTrue(feature.isValid())

    def testDeterminePKOnView(self):
        """
        Determine primary key on a view if primary key constraint (disabled) exists on view
        """

        self.execSQLCommand('DROP TABLE "QGIS"."TABLE_TESTPKS"', ignore_errors=True)
        self.execSQLCommand("""CREATE TABLE "QGIS"."TABLE_TESTPKS" (pk1 INTEGER, DESCRIPTION VARCHAR2(25), pk2 NUMBER, CONSTRAINT cons_pk PRIMARY KEY(pk1, pk2))""")
        self.execSQLCommand("""INSERT INTO "QGIS"."TABLE_TESTPKS" VALUES(1000,'Desc for 1st record', 1)""")
        self.execSQLCommand("""INSERT INTO "QGIS"."TABLE_TESTPKS" VALUES(2000,'Desc for 2nd record', 2)""")
        self.execSQLCommand("""CREATE OR REPLACE VIEW "QGIS"."VIEW_TESTPKS" AS SELECT * FROM "QGIS"."TABLE_TESTPKS" """)

        vl = QgsVectorLayer(
            self.dbconn + ' sslmode=disable table="QGIS"."TABLE_TESTPKS" sql=',
            'test', 'oracle')

        self.assertEqual(vl.dataProvider().pkAttributeIndexes(), [0, 2])

        vl = QgsVectorLayer(
            self.dbconn + ' sslmode=disable table="QGIS"."VIEW_TESTPKS" sql=',
            'test', 'oracle')

        self.assertEqual(vl.dataProvider().pkAttributeIndexes(), [])

        self.execSQLCommand("""ALTER VIEW VIEW_TESTPKS ADD CONSTRAINT const_view_pks PRIMARY KEY (pk1,pk2) DISABLE""")

        vl = QgsVectorLayer(
            self.dbconn + ' sslmode=disable table="QGIS"."VIEW_TESTPKS" sql=',
            'test', 'oracle')

        self.assertEqual(vl.dataProvider().pkAttributeIndexes(), [0, 2])

    def getGeneratedColumnsData(self):
        """
        return a tuple with the generated column test layer and the expected generated value
        """
        return (QgsVectorLayer(self.dbconn + ' sslmode=disable table="QGIS"."GENERATED_COLUMNS"', 'test', 'oracle'),
                """'test:'||TO_CHAR("pk")""")

    def testEvaluateDefaultValues(self):
        """
        Test default values evaluation on with or without option EvaluateDefaultValues
        see https://github.com/qgis/QGIS/issues/39504
        """

        self.execSQLCommand('DROP TABLE "QGIS"."TEST_EVAL_EXPR"', ignore_errors=True)
        self.execSQLCommand("""CREATE TABLE "QGIS"."TEST_EVAL_EXPR" (pk INTEGER, "name" VARCHAR2(100) DEFAULT 'qgis')""")

        vl = QgsVectorLayer(
            self.dbconn + ' sslmode=disable table="QGIS"."TEST_EVAL_EXPR" sql=',
            'test', 'oracle')

        self.assertTrue(vl.isValid())

        # feature with default evaluation clause (not yet evaluated)
        feat1 = QgsFeature(vl.fields())
        feat1.setAttributes([1, "'qgis'"])

        feat2 = QgsFeature(vl.fields())
        feat2.setAttributes([2, 'test'])

        self.assertTrue(vl.dataProvider().addFeatures([feat1, feat2]))

        attributes = [feat.attributes() for feat in vl.getFeatures()]
        self.assertEqual(attributes, [[1, 'qgis'], [2, 'test']])

        vl.dataProvider().setProviderProperty(QgsDataProvider.ProviderProperty.EvaluateDefaultValues, True)

        # feature with already evaluated default value
        feat1 = QgsFeature(vl.fields())
        feat1.setAttributes([3, 'qgis'])

        feat2 = QgsFeature(vl.fields())
        feat2.setAttributes([4, 'test'])

        self.assertTrue(vl.dataProvider().addFeatures([feat1, feat2]))

        attributes = [feat.attributes() for feat in vl.getFeatures()]
        self.assertEqual(attributes, [[1, 'qgis'], [2, 'test'], [3, 'qgis'], [4, 'test']])

    def testCreateEmptyLayer(self):

        # cleanup (it seems overwrite option doesn't clean the sdo_geom_metadata table)
        self.execSQLCommand('DROP TABLE "QGIS"."EMPTY_LAYER"', ignore_errors=True)
        self.execSQLCommand("DELETE FROM user_sdo_geom_metadata  where TABLE_NAME='EMPTY_LAYER'", ignore_errors=True)

        uri = self.dbconn + "srid=4326 type=POINT table=\"EMPTY_LAYER\" (GEOM)"
        exporter = QgsVectorLayerExporter(uri=uri, provider='oracle', fields=QgsFields(), geometryType=QgsWkbTypes.Type.Point, crs=QgsCoordinateReferenceSystem('EPSG:4326'), overwrite=True)
        self.assertEqual(exporter.errorCount(), 0)
        self.assertEqual(exporter.errorCode(), 0)

        # check IF there is an empty table (will throw error if the EMPTY_LAYER table does not exist)
        self.execSQLCommand('SELECT count(*) FROM "QGIS"."EMPTY_LAYER"')

        # check that metadata table has been correctly populated
        query = QSqlQuery(self.conn)
        self.assertTrue(query.exec("SELECT column_name, srid FROM user_sdo_geom_metadata WHERE table_name = 'EMPTY_LAYER'"))
        self.assertTrue(query.next())
        self.assertEqual(query.value(0), "GEOM")
        # Cannot work with proj version < 7 because it cannot identify properly EPSG:4326
        # TODO remove this when PROJ will be >= 7
        if QgsProjUtils.projVersionMajor() >= 7:
            self.assertEqual(query.value(1), 4326)
        query.finish()

        # no feature, so we cannot guess the geometry type, so the layer is not valid
        # but srid is set for provider in case you want to add a feature even if the layer is invalid!
        # layer sourceCrs is empty because the layer is not considered spatial (not know geometry type)
        vl = QgsVectorLayer(self.dbconn + ' sslmode=disable table="QGIS"."EMPTY_LAYER" (GEOM) sql=', 'test', 'oracle')
        self.assertFalse(vl.isValid())
        self.assertEqual(vl.dataProvider().sourceCrs().authid(), "EPSG:4326")

        # so we set the geometry type
        vl = QgsVectorLayer(self.dbconn + ' sslmode=disable type=POINT table="QGIS"."EMPTY_LAYER" (GEOM) sql=', 'test', 'oracle')
        self.assertTrue(vl.isValid())
        self.assertEqual(vl.sourceCrs().authid(), "EPSG:4326")

        f = QgsFeature(vl.fields())
        f.setGeometry(QgsGeometry.fromWkt('POINT (43.5 1.42)'))
        vl.dataProvider().addFeatures([f])

        query = QSqlQuery(self.conn)
        self.assertTrue(query.exec('SELECT "l"."GEOM"."SDO_SRID" from "QGIS"."EMPTY_LAYER" "l"'))
        self.assertTrue(query.next())
        # Cannot work with proj version < 7 because it cannot identify properly EPSG:4326
        # TODO remove this when PROJ will be >= 7
        if QgsProjUtils.projVersionMajor() >= 7:
            self.assertEqual(query.value(0), 4326)
        query.finish()

        # now we can autodetect geom type and srid
        vl = QgsVectorLayer(self.dbconn + ' sslmode=disable table="QGIS"."EMPTY_LAYER" (GEOM) sql=', 'test', 'oracle')
        self.assertTrue(vl.isValid())
        # Cannot work with proj version < 7 because it cannot identify properly EPSG:4326
        # TODO remove this when PROJ will be >= 7
        if QgsProjUtils.projVersionMajor() >= 7:
            self.assertEqual(vl.sourceCrs().authid(), "EPSG:4326")

    def testCreateAspatialLayer(self):
        """
        Test creation of a non-spatial layer
        """

        # cleanup (it seems overwrite option doesn't clean the sdo_geom_metadata table)
        self.execSQLCommand('DROP TABLE "QGIS"."ASPATIAL_LAYER"', ignore_errors=True)

        fields = QgsFields()
        fields.append(QgsField("INTEGER_T", QVariant.Int))

        uri = self.dbconn + "table=\"ASPATIAL_LAYER\""
        exporter = QgsVectorLayerExporter(uri=uri, provider='oracle', fields=fields, geometryType=QgsWkbTypes.Type.NoGeometry, crs=QgsCoordinateReferenceSystem(), overwrite=True)
        self.assertEqual(exporter.errorCount(), 0)
        self.assertEqual(exporter.errorCode(), 0)

        self.execSQLCommand('SELECT count(*) FROM "QGIS"."ASPATIAL_LAYER"')
        vl = QgsVectorLayer(self.dbconn + ' sslmode=disable table="QGIS"."ASPATIAL_LAYER" sql=', 'test', 'oracle')
        self.assertTrue(vl.isValid())

        self.assertEqual(vl.fields().names(), ["INTEGER_T"])

    def testCreateInvalidLayer(self):
        """
        Test creation of an invalid layer (no geometry, no column)
        """

        # cleanup (it seems overwrite option doesn't clean the sdo_geom_metadata table)
        self.execSQLCommand('DROP TABLE "QGIS"."INVALID_LAYER"', ignore_errors=True)

        fields = QgsFields()

        uri = self.dbconn + "table=\"INVALID_LAYER\""
        exporter = QgsVectorLayerExporter(uri=uri, provider='oracle', fields=fields, geometryType=QgsWkbTypes.Type.NoGeometry, crs=QgsCoordinateReferenceSystem(), overwrite=True)
        self.assertEqual(exporter.errorCode(), QgsVectorLayerExporter.ExportError.ErrCreateDataSource)

    def testAddEmptyFeature(self):
        """
        Test inserting a feature with only null attributes
        """

        def countFeature(table_name):
            self.assertTrue(self.conn)
            query = QSqlQuery(self.conn)
            res = query.exec(f'SELECT count(*) FROM "QGIS"."{table_name}"')
            self.assertTrue(query.next())
            count = query.value(0)
            query.finish()
            return count

        self.execSQLCommand('DROP TABLE "QGIS"."EMPTYFEATURE_LAYER"', ignore_errors=True)
        self.execSQLCommand('CREATE TABLE "QGIS"."EMPTYFEATURE_LAYER" ( "num" INTEGER, GEOM SDO_GEOMETRY)')

        vl = QgsVectorLayer(self.dbconn + ' sslmode=disable type=Point table="QGIS"."EMPTYFEATURE_LAYER" (GEOM) sql=', 'test', 'oracle')
        self.assertTrue(vl.isValid())

        # add feature with no attributes, no geometry
        feature = QgsFeature(vl.fields())
        self.assertTrue(vl.dataProvider().addFeatures([feature])[0])
        self.assertEqual(countFeature('EMPTYFEATURE_LAYER'), 1)

        # add feature with no attribute and one geometry
        feature = QgsFeature(vl.fields())
        feature.setGeometry(QgsGeometry.fromWkt('Point (43.5 1.42)'))
        self.assertTrue(vl.dataProvider().addFeatures([feature])[0])
        self.assertEqual(countFeature('EMPTYFEATURE_LAYER'), 2)

        self.execSQLCommand('DROP TABLE "QGIS"."EMPTYFEATURE_NOGEOM_LAYER"', ignore_errors=True)
        self.execSQLCommand('CREATE TABLE "QGIS"."EMPTYFEATURE_NOGEOM_LAYER" ( "num" INTEGER)')

        # same tests but with no geometry in table definition
        vl = QgsVectorLayer(self.dbconn + ' sslmode=disable table="QGIS"."EMPTYFEATURE_NOGEOM_LAYER" sql=', 'test', 'oracle')
        self.assertTrue(vl.isValid())

        # add feature with no attributes
        feature = QgsFeature(vl.fields())
        self.assertTrue(vl.dataProvider().addFeatures([feature])[0])
        self.assertEqual(countFeature('EMPTYFEATURE_NOGEOM_LAYER'), 1)

    def testCreateLayerLongFieldNames(self):
        """
        Test to create an empty layer with long field names
        """

        # cleanup (it seems overwrite option doesn't clean the sdo_geom_metadata table)
        self.execSQLCommand('DROP TABLE "QGIS"."LONGFIELD_LAYER"', ignore_errors=True)

        long_name = "this_is_a_very_long_field_name_more_than_30_characters"

        fields = QgsFields()
        fields.append(QgsField(long_name, QVariant.Int))

        uri = self.dbconn + "table=\"LONGFIELD_LAYER\""
        exporter = QgsVectorLayerExporter(uri=uri, provider='oracle', fields=fields, geometryType=QgsWkbTypes.Type.Point, crs=QgsCoordinateReferenceSystem("EPSG:4326"), overwrite=True)
        self.assertEqual(exporter.errorCount(), 0)
        self.assertEqual(exporter.errorCode(), 0)

        self.execSQLCommand('SELECT count(*) FROM "QGIS"."LONGFIELD_LAYER"')
        vl = QgsVectorLayer(self.dbconn + ' sslmode=disable table="QGIS"."LONGFIELD_LAYER" sql=', 'test', 'oracle')
        self.assertTrue(vl.isValid())

        self.assertEqual(vl.fields().names(), [long_name])

    def testCreateGeomLowercase(self):
        """
        Test to create an empty layer with either table or geometry column in lower case. It has to fail
        """

        # table is lower case -> fails
        self.execSQLCommand('DROP TABLE "QGIS"."lowercase_layer"', ignore_errors=True)

        fields = QgsFields()
        fields.append(QgsField("test", QVariant.Int))

        uri = self.dbconn + "table=\"lowercase_layer\" (GEOM)"
        exporter = QgsVectorLayerExporter(uri=uri, provider='oracle', fields=fields, geometryType=QgsWkbTypes.Type.Point, crs=QgsCoordinateReferenceSystem("EPSG:4326"), overwrite=True)
        self.assertEqual(exporter.errorCode(), 2)

        # geom column is lower case -> fails
        self.execSQLCommand('DROP TABLE "QGIS"."LOWERCASEGEOM_LAYER"', ignore_errors=True)

        fields = QgsFields()
        fields.append(QgsField("test", QVariant.Int))

        uri = self.dbconn + "table=\"LOWERCASEGEOM\" (geom)"
        exporter = QgsVectorLayerExporter(uri=uri, provider='oracle', fields=fields, geometryType=QgsWkbTypes.Type.Point, crs=QgsCoordinateReferenceSystem("EPSG:4326"), overwrite=True)
        self.assertEqual(exporter.errorCode(), 2)

        # table and geom column are uppercase -> success
        self.execSQLCommand('DROP TABLE "QGIS"."UPPERCASEGEOM_LAYER"', ignore_errors=True)
        self.execSQLCommand("""DELETE FROM user_sdo_geom_metadata  where TABLE_NAME = 'UPPERCASEGEOM_LAYER'""")

        fields = QgsFields()
        fields.append(QgsField("test", QVariant.Int))

        uri = self.dbconn + "table=\"UPPERCASEGEOM_LAYER\" (GEOM)"

        exporter = QgsVectorLayerExporter(uri=uri, provider='oracle', fields=fields, geometryType=QgsWkbTypes.Type.Point, crs=QgsCoordinateReferenceSystem("EPSG:4326"), overwrite=True)
        print(exporter.errorMessage())
        self.assertEqual(exporter.errorCount(), 0)
        self.assertEqual(exporter.errorCode(), 0)

    def testDetectedGeomType(self):
        """
        Test detected geom type when no one is required
        """

        testdata = [
            ("POINT", 2, "SDO_GEOMETRY( 2001,5698,SDO_POINT_TYPE(1, 2, NULL), NULL, NULL)", QgsWkbTypes.Type.Point),
            ("POINTZ", 3, "SDO_GEOMETRY( 3001,5698,SDO_POINT_TYPE(1, 2, 3), NULL, NULL)", QgsWkbTypes.Type.PointZ),
            # there is difference between line and curve so everything is a compoundcurve to cover both
            # https://docs.oracle.com/database/121/SPATL/sdo_geometry-object-type.htm
            ("LINE", 2, "SDO_GEOMETRY( 2002,5698,NULL, SDO_ELEM_INFO_ARRAY(1,2,1), SDO_ORDINATE_ARRAY(1,2,3,4,5,6))", QgsWkbTypes.Type.CompoundCurve),
            ("LINEZ", 3, "SDO_GEOMETRY(3002,5698,NULL, SDO_ELEM_INFO_ARRAY(1,2,1), SDO_ORDINATE_ARRAY(1,2,3,4,5,6,7,8,9))", QgsWkbTypes.Type.CompoundCurveZ),
            ("CURVE", 2, "SDO_GEOMETRY(2002,5698,NULL, SDO_ELEM_INFO_ARRAY(1, 2, 2), SDO_ORDINATE_ARRAY(1, 2, 5, 4, 7, 2.2, 10, .1, 13, 4))", QgsWkbTypes.Type.CompoundCurve),
            ("CURVEZ", 3, "SDO_GEOMETRY(3002,5698,NULL, SDO_ELEM_INFO_ARRAY(1, 2, 2), SDO_ORDINATE_ARRAY(1, 2, 1, 5, 4, 2, 7, 2.2, 3, 10, 0.1, 4, 13, 4, 5))", QgsWkbTypes.Type.CompoundCurveZ),
            ("POLYGON", 2, "SDO_GEOMETRY(2003,5698,NULL, SDO_ELEM_INFO_ARRAY(1,1003,1), SDO_ORDINATE_ARRAY(1, 2, 11, 2, 11, 22, 1, 22, 1, 2))", QgsWkbTypes.Type.Polygon),
            ("POLYGONZ", 3, "SDO_GEOMETRY(3003,5698,NULL, SDO_ELEM_INFO_ARRAY(1,1003,1), SDO_ORDINATE_ARRAY(1, 2, 3, 11, 2, 13, 11, 22, 15, 1, 22, 7, 1, 2, 3))", QgsWkbTypes.Type.PolygonZ),

            ("MULTIPOINT", 2, "SDO_GEOMETRY( 2005,5698,NULL, sdo_elem_info_array (1,1,1, 3,1,1), sdo_ordinate_array (1,2, 3,4))", QgsWkbTypes.Type.MultiPoint),
            ("MULTIPOINTZ", 3, "SDO_GEOMETRY( 3005,5698,NULL, sdo_elem_info_array (1,1,2), sdo_ordinate_array (1,2,3, 4,5,6))", QgsWkbTypes.Type.MultiPointZ),
            # there is difference between line and curve so everything is a compoundcurve to cover both
            # https://docs.oracle.com/database/121/SPATL/sdo_geometry-object-type.htm
            ("MULTILINE", 2, "SDO_GEOMETRY(2006,5698,NULL, SDO_ELEM_INFO_ARRAY(1,2,1, 5,2,1), SDO_ORDINATE_ARRAY(1, 2, 3, 4, 5, 6, 7, 8, 9, 10))", QgsWkbTypes.Type.MultiCurve),
            ("MULTILINEZ", 3, "SDO_GEOMETRY(3006,5698,NULL, SDO_ELEM_INFO_ARRAY(1,2,1, 7,2,1), SDO_ORDINATE_ARRAY(1, 2, 11, 3, 4, -11, 5, 6, 9, 7, 8, 1, 9, 10, -3))", QgsWkbTypes.Type.MultiCurveZ),
            ("MULTICURVE", 2, "SDO_GEOMETRY(2006,5698,NULL, SDO_ELEM_INFO_ARRAY(1,2,2, 11,2,2), SDO_ORDINATE_ARRAY(1, 2, 5, 4, 7, 2.2, 10, .1, 13, 4, -11, -3, 5, 7, 10, -1))", QgsWkbTypes.Type.MultiCurve),
            ("MULTICURVEZ", 3, "SDO_GEOMETRY(3006,5698,NULL, SDO_ELEM_INFO_ARRAY(1,2,2, 16,2,2), SDO_ORDINATE_ARRAY(1, 2, 1, 5, 4, 2, 7, 2.2, 3, 10, .1, 4, 13, 4, 5, -11, -3, 6, 5, 7, 8, 10, -1, 9))", QgsWkbTypes.Type.MultiCurveZ),
            ("MULTIPOLYGON", 2, "SDO_GEOMETRY(2007,5698,NULL, SDO_ELEM_INFO_ARRAY(1,1003,1, 11,1003,1, 21,2003,1, 29,2003,1), SDO_ORDINATE_ARRAY(1, 2, 11, 2, 11, 22, 1, 22, 1, 2, 1, 2, 11, 2, 11, 22, 1, 22, 1, 2, 5, 6, 8, 9, 8, 6, 5, 6, 3, 4, 5, 6, 3, 6, 3, 4))", QgsWkbTypes.Type.MultiPolygon),
            ("MULTIPOLYGONZ", 3, "SDO_GEOMETRY(3007,5698,NULL, SDO_ELEM_INFO_ARRAY(1,1003,1, 16,1003,1, 31,2003,1), SDO_ORDINATE_ARRAY(1, 2, 3, 11, 2, 13, 11, 22, 15, 1, 22, 7, 1, 2, 3, 1, 2, 3, 11, 2, 13, 11, 22, 15, 1, 22, 7, 1, 2, 3, 5, 6, 1, 8, 9, -1, 8, 6, 2, 5, 6, 1))", QgsWkbTypes.Type.MultiPolygonZ)
        ]

        for name, dim, geom, wkb_type in testdata:
            # We choose SRID=5698 (see https://docs.oracle.com/database/121/SPATL/three-dimensional-coordinate-reference-system-support.htm#SPATL626)
            # to get Oracle valid geometries because it support 3D and arcs (arcs are not supported in geodetic projection)
            self.createTable(f'DETECT_{name}', dim, 5698)
            self.execSQLCommand(f'INSERT INTO "QGIS"."DETECT_{name}" ("pk", GEOM) SELECT 1, {geom} from dual')

            layer = QgsVectorLayer(
                self.dbconn + f' sslmode=disable key=\'pk\' srid=3857 table="QGIS"."DETECT_{name}" (GEOM) sql=', f'test{name}', 'oracle')
            self.assertTrue(layer.isValid())
            self.assertEqual(layer.wkbType(), wkb_type)

            self.execSQLCommand(f'DROP TABLE "QGIS"."DETECT_{name}"')

    def testCredentialsCache(self):
        """
        Test that credentials are correctly cached when using
        """
        # self.dbconn remove user/pwd

        # get username/password from uri

        user_pattern = "user='([^']*)'"
        pwd_pattern = "password='([^']*)'"

        rematch = re.search(user_pattern, self.dbconn)
        self.assertTrue(rematch)
        self.assertEqual(len(rematch.groups()), 1)
        username = rematch.groups()[0]

        rematch = re.search(pwd_pattern, self.dbconn)
        self.assertTrue(rematch)
        self.assertEqual(len(rematch.groups()), 1)
        password = rematch.groups()[0]

        conn_wo_login_pwd = re.sub(user_pattern, "", self.dbconn)
        conn_wo_login_pwd = re.sub(pwd_pattern, "", conn_wo_login_pwd)

        class TestCredentials(QgsCredentials):

            def __init__(self, username, pwd):
                super().__init__()
                self.setInstance(self)
                self.nbCall = 0
                self.username = username
                self.pwd = pwd

            def request(self, realm, username, pwd, msg):
                self.nbCall += 1
                return (True, self.username, self.pwd)

        credentials = TestCredentials(username, password)
        self.assertEqual(credentials, QgsCredentials.instance())

        # no user/pwd -> credential is called
        transaction = QgsProviderRegistry.instance().createTransaction("oracle", conn_wo_login_pwd)
        self.assertEqual(transaction.begin()[0], True)
        self.assertEqual(credentials.nbCall, 1)

        # no user/pwd second times -> use cache
        transaction = QgsProviderRegistry.instance().createTransaction("oracle", conn_wo_login_pwd)
        self.assertEqual(transaction.begin()[0], True)
        self.assertEqual(credentials.nbCall, 1)

        # same connection, different user, don't use cache (credentials.nbCall is incremented)
        transaction = QgsProviderRegistry.instance().createTransaction("oracle", conn_wo_login_pwd + " user='titi'")
        self.assertEqual(transaction.begin()[0], True)  # test credentials always return valid credentials so it's valid, but we don't care
        self.assertEqual(credentials.nbCall, 2)


if __name__ == '__main__':
    unittest.main()
